﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spSecurity_RevokeAccess]
#-- Purpose:		Removes a user access right.  If all rights are gone, removes
#--					the user's connection rights and existence in the database.
#--					If the user does not have access to any databases, the login
#--					is deleted.
#--	Last Update:	11/23/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spSecurity_RevokeAccess]
(
	@database_name	sysname,
	@role_name		sysname,
	@user_name		sysname,
	@is_role		bit				= 1
)
AS

SET NOCOUNT ON

BEGIN TRY

	DECLARE @msg nvarchar(4000), @progname sysname

	--- Define the name of this program (for logs)
	SELECT		@progname = 'spSecurity_RevokeAccess'

	SELECT	@msg = '[spSecurity_RevokeAccess] Started -  User: ' + @user_name + ', Role: ' + @role_name
	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @msg

	--- Declare Local Variables
	DECLARE		@user_access TABLE (database_name sysname, username sysname)
	DECLARE		@db_name sysname, @sSQL nvarchar(MAX)

	--- *********************************************************************************
	--- Remove the requested rights removal
	---
	SELECT	@sSQL = 'USE ' + QUOTENAME(@database_name) + '
	DECLARE @msg nvarchar(MAX)

	IF EXISTS (
		SELECT		TOP 1 *
		FROM		sys.database_principals r
		JOIN		sys.database_role_members rm ON r.principal_id = rm.role_principal_id
					AND r.name = N''' + @role_name + '''
		JOIN		sys.database_principals m ON m.principal_id = rm.member_principal_id
					AND m.name = N''' + @user_name + '''
				)
	  BEGIN
		SELECT	@msg = ''  ...Revoking rights - User: ' + @user_name + ', Role: ' + @role_name + '''
		EXEC	[' + DB_NAME() + '].[dbo].[spMaint_InsertLogEntry] @program_name = ''' + @progname + ''', @message = @msg
		EXECUTE sp_droprolemember @rolename = N''' + @role_name + ''', @membername = N''' + @user_name + '''
	  END'
	EXEC	(@sSQL)

	--- *********************************************************************************
	--- If the user has no more rights to the database, remove the user from the database
	---
	SELECT	@sSQL = 'USE ' + QUOTENAME(@database_name) + '
	DECLARE @msg nvarchar(MAX)

	IF NOT EXISTS  (
			SELECT		TOP 1 *
			FROM		sys.database_principals r
			JOIN		sys.database_role_members rm ON r.principal_id = rm.role_principal_id
			JOIN		sys.database_principals m ON m.principal_id = rm.member_principal_id
						AND m.name = N''' + @user_name + '''
					)
		AND EXISTS (
			SELECT		TOP 1 *
			FROM		sys.database_principals m
			WHERE		m.name = N''' + @user_name + '''
					)
	  BEGIN
		SELECT	@msg = ''  ...Revoking access - User: ' + @user_name + ', Role: ' + @role_name + '''
		EXEC	[' + DB_NAME() + '].[dbo].[spMaint_InsertLogEntry] @program_name = ''' + @progname + ''', @message = @msg

		REVOKE CONNECT FROM ' + QUOTENAME(@user_name) + '

		IF EXISTS (SELECT TOP 1 * FROM sys.schemas WHERE name = N''' + @user_name + ''')
		  BEGIN
			SELECT	@msg = ''  ...Dropping database schema - User: ' + @user_name + ', Role: ' + @role_name + '''
			EXEC	[' + DB_NAME() + '].[dbo].[spMaint_InsertLogEntry] @program_name = ''' + @progname + ''', @message = @msg

			DROP SCHEMA ' + QUOTENAME(@user_name) + '
		  END

		SELECT	@msg = ''  ...Dropping database user - User: ' + @user_name + ', Role: ' + @role_name + '''
		EXEC	[' + DB_NAME() + '].[dbo].[spMaint_InsertLogEntry] @program_name = ''' + @progname + ''', @message = @msg

		DROP USER ' + QUOTENAME(@user_name) + '
	  END'
	EXEC	(@sSQL)

	--- *********************************************************************************
	--- If the user has no more rights to any database, remove the login from the server
	---
	DECLARE crsDB CURSOR FOR SELECT name FROM master.sys.databases
	OPEN crsDB
	FETCH NEXT FROM crsDB INTO @db_name
	WHILE @@FETCH_STATUS <> -1
	  BEGIN
		SELECT	@sSQL = 'USE ' + QUOTENAME(@db_name) + '
				SELECT		DB_NAME() database_name,
							name
				FROM		' + QUOTENAME(@db_name) + '.[sys].[database_principals]
				WHERE		name = ''' + @user_name + ''''
		INSERT INTO @user_access
			EXEC	(@sSQL)

		FETCH NEXT FROM crsDB INTO @db_name
	  END
	CLOSE crsDB
	DEALLOCATE crsDB

	IF NOT EXISTS (SELECT TOP 1 * FROM @user_access) 
		AND EXISTS (SELECT * FROM master.sys.syslogins WHERE name = @user_name)
		AND NOT EXISTS (
			SELECT		m.[name] [MemberName],
						r.[name] [RoleName]
			FROM		[sys].[server_principals] m
			JOIN		[sys].[server_role_members] rm ON rm.[member_principal_id] = m.[principal_id]
			JOIN		[sys].[server_principals] r ON rm.[role_principal_id] = r.[principal_id]
			WHERE		m.[name] = @user_name
			)
	  BEGIN
		SELECT	@msg = '  ...Dropping login - User: ' + @user_name + ', Role: ' + @role_name
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @msg

		SELECT	@sSQL = 'DROP LOGIN ' + QUOTENAME(@user_name)
		EXEC	(@sSQL)
	  END

	SELECT	@msg = '[spSecurity_RevokeAccess] Completed - User: ' + @user_name + ', Role: ' + @role_name
	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @msg
END TRY

BEGIN CATCH

	PRINT 'ERROR: ' + ERROR_MESSAGE()

END CATCH

SET NOCOUNT OFF
